﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Model;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
     public  class RegardDAL
    {
        /// <summary>
        /// 链接数据库
        /// </summary>
        Database db = DatabaseFactory.CreateDatabase("constring");
        /// <summary>
        /// 收藏文章总数
        /// </summary>
        /// <returns></returns>
        public int CCount(int Login_Id)
        {
            try
            {
                int Count = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM (select ROW_NUMBER() over(order by Regard_id)ID,t.*,Users.Login_Name,Users.Login_Nickname from ( select Articlescrap.Articlescrap_Title,Articlescrap.Articlescrap_Content,Articlescrap_date,Regard.Regard_id,Regard.Articlescrap_Id,Regard.Login_Id,Regard.STATE  from Articlescrap join Regard on Articlescrap.Articlescrap_Id=Regard.Articlescrap_Id )t join Users on t.Login_Id=Users.Login_Id where t.Login_Id=" + Login_Id + " AND t.[STATE]=1)d"));
                return Count;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("获取数据异常");
            }
            finally
            {

            }
        }
         /// <summary>
         /// 获取用户收藏的文章
         /// </summary>
         /// <param name="Login_Id"></param>
         /// <returns></returns>
        public List<RegardModel> GetRegard(int Login_Id,int PageIndex,int PageSize)
        {
            try
            {
                int GetPageIndex = (PageIndex - 1) * PageSize + 1;
                int GetPageSize = PageIndex * PageSize;
                DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from (select ROW_NUMBER() over(order by Regard_id)ID,t.*,Users.Login_Name,Users.Login_Nickname from ( select Articlescrap.Articlescrap_Title,Articlescrap.Articlescrap_Content,Articlescrap_date,Regard.Regard_id,Regard.Articlescrap_Id,Regard.Login_Id,Regard.STATE  from Articlescrap join Regard on Articlescrap.Articlescrap_Id=Regard.Articlescrap_Id )t join Users on t.Login_Id=Users.Login_Id where t.Login_Id=" + Login_Id + " AND t.[STATE]=1)d where ID between " + GetPageIndex + " and " + GetPageSize + "");
             List<RegardModel> list = new List<RegardModel>();
             foreach (DataRow dr in ds.Tables[0].Rows)
             {
                  RegardModel Regard = new RegardModel();
                  Regard.Articlescrap_Content = dr["Articlescrap_Content"].ToString();
                  Regard.Articlescrap_date = dr["Articlescrap_date"].ToString();
                  Regard.Articlescrap_Id = Convert.ToInt32(dr["Articlescrap_Id"]);
                  Regard.Articlescrap_Title = dr["Articlescrap_Title"].ToString();
                  Regard.Login_Id = Convert.ToInt32(dr["Login_Id"]);
                  Regard.Login_Name = dr["Login_Name"].ToString();
                  Regard.Regard_id = Convert.ToInt32(dr["Regard_id"]);
                  Regard.Login_Nickname = dr["Login_Nickname"].ToString();
                  list.Add(Regard);
              }
             return list;
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("获取数据异常");
            }
            finally{
             
            }
           
           
        }
        /// <summary>
        /// 获取用户收藏的文章详细信息
        /// </summary>
        /// <param name="Login_Id"></param>
        /// <returns></returns>
        public RegardModel GetRegardId(int Login_Id,int Id)
        {
            try {
                DataSet ds = db.ExecuteDataSet(CommandType.Text, "select t.*,Users.Login_Name,Users.Login_Nickname from ( select Articlescrap.Articlescrap_Title,Articlescrap.Articlescrap_Content,Articlescrap_date,Regard.* from Articlescrap join Regard on Articlescrap.Articlescrap_Id=Regard.Articlescrap_Id )t join Users on t.Login_Id=Users.Login_Id where t.Login_Id=" + Login_Id + " and Articlescrap_Id=" + Id + "");
                RegardModel Regard = new RegardModel();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Regard.Articlescrap_Content = dr["Articlescrap_Content"].ToString();
                    Regard.Articlescrap_date = dr["Articlescrap_date"].ToString();
                    Regard.Articlescrap_Id = Convert.ToInt32(dr["Articlescrap_Id"]);
                    Regard.Articlescrap_Title = dr["Articlescrap_Title"].ToString();
                    Regard.Login_Id = Convert.ToInt32(dr["Login_Id"]);
                    Regard.Login_Name = dr["Login_Name"].ToString();
                    Regard.Regard_id = Convert.ToInt32(dr["Regard_id"]);
                    Regard.Login_Nickname = dr["Login_Nickname"].ToString();
                }
                return Regard;
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("获取数据异常");
            }
            finally{
            
            }
            
        }
         /// <summary>
         /// 文章总数
         /// </summary>
         /// <returns></returns>
        public int Count()
        {
            try
            {
                int Count =Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "  SELECT COUNT(*) FROM (select ROW_NUMBER() over(order by Articlescrap_Id)ID,Articlescrap.Articlescrap_Id, Articlescrap.Articlescrap_Title,Articlescrap.Articlescrap_Content,Articlescrap_date,Users.Login_Nickname ,Users.Login_Name from Articlescrap join Users on Articlescrap.Login_Id=Users.Login_Id)t"));
                return Count;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("获取数据异常");
            }
            finally
            {

            }
        }
        /// <summary>
        /// 获取文章
        /// </summary>
        /// <param name="Login_Id"></param>
        /// <returns></returns>
        public List<RegardModel> GetRegardALL(int PageIndex, int PageSize)
        {
            try
            {
                int GetPageIndex = (PageIndex - 1) * PageSize + 1;
                int GetPageSize = PageIndex * PageSize;
                DataSet ds = db.ExecuteDataSet(CommandType.Text, "  select * from (select ROW_NUMBER() over(order by Articlescrap_Id)ID,Articlescrap.Articlescrap_Id, Articlescrap.Articlescrap_Title,Articlescrap.Articlescrap_Content,Articlescrap_date,Users.Login_Nickname ,Users.Login_Name from Articlescrap join Users on Articlescrap.Login_Id=Users.Login_Id)t where ID between " + GetPageIndex + " and " + GetPageSize + "");
                List<RegardModel> list = new List<RegardModel>();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    RegardModel Regard = new RegardModel();
                    Regard.Articlescrap_Content = dr["Articlescrap_Content"].ToString();
                    Regard.Articlescrap_date = dr["Articlescrap_date"].ToString();
                    Regard.Articlescrap_Id = Convert.ToInt32(dr["Articlescrap_Id"]);
                    Regard.Articlescrap_Title = dr["Articlescrap_Title"].ToString();
                    Regard.Login_Name = dr["Login_Name"].ToString();
                    Regard.Login_Nickname = dr["Login_Nickname"].ToString();
                    list.Add(Regard);
                }
                return list;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("获取数据异常");
            }
            finally
            {

            }


        }
        /// <summary>
        /// 获取文章详细信息
        /// </summary>
        /// <param name="Login_Id"></param>
        /// <returns></returns>
        public RegardModel GetRegardIdAll(int Id)
        {
            try
            {
                DataSet ds = db.ExecuteDataSet(CommandType.Text, "SELECT Articlescrap.Articlescrap_Id, Articlescrap.Articlescrap_Title,Articlescrap.Articlescrap_Content,Articlescrap_date,Users.Login_Nickname ,Users.Login_Name from Articlescrap join Users on Articlescrap.Login_Id=Users.Login_Id  where Articlescrap_Id=" + Id + "");
                RegardModel Regard = new RegardModel();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Regard.Articlescrap_Content = dr["Articlescrap_Content"].ToString();
                    Regard.Articlescrap_date = dr["Articlescrap_date"].ToString();
                    Regard.Articlescrap_Id = Convert.ToInt32(dr["Articlescrap_Id"]);
                    Regard.Articlescrap_Title = dr["Articlescrap_Title"].ToString();
                    Regard.Login_Name = dr["Login_Name"].ToString();
                    Regard.Login_Nickname = dr["Login_Nickname"].ToString();
                }
                return Regard;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("获取数据异常");
            }
            finally
            {

            }

        }
         /// <summary>
         /// 判断文章是否已经收藏
         /// </summary>
         /// <param name="Id"></param>
         /// <returns></returns>
        public int AddRegard(int Login_Id,int Id)
        {
            try
            {
                int Count = Convert.ToInt32(db.ExecuteScalar(CommandType.Text, "SELECT r.Regard_id, r.Articlescrap_Id, r.Login_Id FROM Regard AS r WHERE r.Login_Id="+Login_Id+" and r.Articlescrap_Id=" + Id + ""));
                return Count;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("获取数据异常");
            }
            finally
            {

            }
        }
        /// <summary>
        /// 收藏
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public int AddRegardDo(int Login_Id,int Id)
        {
            try
            {
                return Convert.ToInt32(db.ExecuteNonQuery(CommandType.Text, "insert into Regard(Articlescrap_Id,Login_Id) values(" + Id + "," + Login_Id + ")"));
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("获取数据异常");
            }
            finally
            {

            }
        }
         /// <summary>
         /// 收藏文章伪删除
         /// </summary>
         /// <param name="Login_Id"></param>
         /// <param name="Id"></param>
         /// <returns></returns>
        public int Del(int Login_Id,int Id)
        {
            try
            {
                return Convert.ToInt32(db.ExecuteNonQuery(CommandType.Text, "UPDATE Regard SET STATE=0 WHERE Articlescrap_Id=" + Id + " AND Login_Id=" + Login_Id + ""));
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw new Exception("删除数据异常");
            }
            finally
            {

            }
        }
    }
}
